﻿using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data.SQLite;

namespace UploadServer
{
	class HostSqlite
	{
		private static string DbFile
		{
			get { return Path.Combine(Environment.CurrentDirectory, "site.sqlite"); }
		}
		private static bool DbExists
		{
			get { return File.Exists(DbFile); }
		}
		private static string ConnectString
		{
			get
			{
				var builder = new SQLiteConnectionStringBuilder();
				builder.DataSource = DbFile;
				return builder.ConnectionString;
			}
		}
		private static void _Init()
		{
			if (DbExists) return;
			string sql1 = "CREATE TABLE IF NOT EXISTS sessions("
				+ "id INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ "session VARCHAR(64) UNIQUE NOT NULL,"
				+ "state INTEGER DEFAULT(0),"
				+ "ts DATETIME DEFAULT(CURRENT_TIMESTAMP)"
				+ ");"
				+ "CREATE INDEX idx_sessions_state ON sessions(state);"
				+ "CREATE INDEX idx_sessions_ts ON sessions(ts);"
				;
			string sql2 = "CREATE TABLE IF NOT EXISTS files("
				+ "id INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ "session INTEGER DEFAULT(0) NOT NULL,"
				+ "guid VARCHAR(64) UNIQUE NOT NULL,"
				+ "state INTEGER DEFAULT(0),"
				+ "ts DATETIME DEFAULT(CURRENT_TIMESTAMP)"
				+ ");"
				+ "CREATE INDEX idx_files_session ON files(session);"
				+ "CREATE INDEX idx_files_state ON files(state);"
				+ "CREATE INDEX idx_files_ts ON files(ts);"
				;
			string sql3 = "CREATE TABLE IF NOT EXISTS users("
				+ "id INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ "name VARCHAR(64) UNIQUE NOT NULL,"
				+ "password VARCHAR(128) NOT NULL,"
				+ "salt VARCHAR(32) NOT NULL,"
				+ "state INTEGER DEFAULT(0),"
				+ "ts DATETIME DEFAULT(CURRENT_TIMESTAMP)"
				+ ");"
				+ "CREATE INDEX idx_users_password ON users(password);"
				+ "CREATE INDEX idx_users_salt ON users(salt);"
				+ "CREATE INDEX idx_users_state ON users(state);"
				+ "CREATE INDEX idx_users_ts ON users(ts);"
				;

			using (var conn = new SQLiteConnection(ConnectString))
			{
				conn.Open();

				using (var cmd = conn.CreateCommand())
				{
					cmd.CommandText = sql1 + sql2 + sql3;
					cmd.ExecuteNonQuery();
				}
				conn.Close();
			}
		}
		private static bool _Inited = false;
		public static void Init()
		{
			if(!_Inited)
			{
				_Inited = true;
				try
				{
					_Init();
				}
				catch (Exception ex)
				{
					File.Delete(DbFile);
					throw ex;
				}
			}
		}

		private static int NonQuery(string sql, params SQLiteParameter[] ps)
		{
			int i;
			using (var conn = new SQLiteConnection(ConnectString))
			{
				conn.Open();
				using (var cmd = conn.CreateCommand())
				{
					//cmd.Transaction.Connection.BeginTransaction(System.Data.IsolationLevel.)
					cmd.CommandText = sql;
					if (ps != null)
					{
						foreach (var p in ps)
						{
							cmd.Parameters.Add(p);
						}
					}
					i = cmd.ExecuteNonQuery();
				}
				conn.Close();
			}
			return i;
		}

		private static object Scalar(string sql, params SQLiteParameter[] ps)
		{
			object i;
			using (var conn = new SQLiteConnection(ConnectString))
			{
				conn.Open();
				using (var cmd = conn.CreateCommand())
				{
					//cmd.Transaction.Connection.BeginTransaction(System.Data.IsolationLevel.)
					cmd.CommandText = sql;
					if (ps != null)
					{
						foreach (var p in ps)
						{
							cmd.Parameters.Add(p);
						}
					}
					i = cmd.ExecuteScalar();
				}
				conn.Close();
			}
			return i;
		}

		public static string NewSession(out int rowid)
		{
			var session = Helper.NewSession();
			string sql = String.Format("INSERT INTO sessions(session)VALUES('{0}');SELECT last_insert_rowid();", session);
			var id = Scalar(sql).ToString();
			int.TryParse(id, out rowid);
			return session;
		}

		public static bool UserExists(string name)
		{
			string sql = "SELECT EXISTS(SELECT id FROM users WHERE name=@name)";
			var _name = new SQLiteParameter();
			_name.ParameterName = "@name";
			_name.DbType = System.Data.DbType.String;
			_name.Value = name;
			var c = Scalar(sql, _name).ToString();
			return int.Parse(c) > 0;
		}

		public static int NewUser(string name, string password)
		{

			if (UserExists(name)) return -1;

			var salt = Helper.Salt();
			var sql = "INSERT INTO users(name,password,salt)VALUES(@name,@password,@salt);SELECT last_insert_rowid();";

			var _name = new SQLiteParameter();
			_name.ParameterName = "@name";
			_name.DbType = System.Data.DbType.String;
			_name.Value = name;

			var _pwd = new SQLiteParameter();
			_pwd.ParameterName = "@password";
			_pwd.DbType = System.Data.DbType.String;
			_pwd.Value = Helper.Sha1(Helper.Md5(password) + salt);
			

			var _salt = new SQLiteParameter();
			_salt.ParameterName = "@salt";
			_salt.DbType = System.Data.DbType.String;
			_salt.Value = name;

			var id = Scalar(sql, _name, _pwd, _salt).ToString();
			return int.Parse(id);

		}

		private static System.Security.Cryptography.RSACryptoServiceProvider RSA
			= new System.Security.Cryptography.RSACryptoServiceProvider();
		public static void UserLogin()
		{
			RSA.FromXmlString("<RSAKeyValue><Modulus>qm76EMpDYXPZr4Lfi+jGzBmkVkrvZUtssNb4qQ2v5+CyndFkLvqVjhyT8a+llOQ9GQtdKs2rGNeUynlMKMuT4WMTLQxB2gVWY5H1TIUI7vWyCUy3n6tT0bu51QHdqIwD6/3SRTtck0VZK8PNkOBwL8GRzCPkgzzrdXVSFsQU4CE=</Modulus><Exponent>AQAB</Exponent><P>10SL4IrhXYhppCoOs95+eEsZ6L7T2QG5ldL1SvJWf9TnML30UErCwFtcHP6KGzvpkkV3o+pS6W4Yx2MJfMG4ZQ==</P><Q>yq6u8ClLbYHIiltzx/2OnBmfe8FpYT2dCNfwTSJ2MDu/JyfWd3U5OkDA4S9Z8eHpEkFmJ41HBPgGovi+lcjHDQ==</Q><DP>YyrpwUxZW8xO1lilo0j5PBNk4Sqfz1WTGCkx5Q/Ut1juFC890ItQQmsT+SttlfQ4urCT2AhHn8+fTKMcKMPZXQ==</DP><DQ>Zo7hwnCC1FSZrzj7azUoVmC4zJxfwG626WSrGbzYZSu2XA/Se/v7Czj84HVfY//WOgCRum3IhAElCo4nkg9YeQ==</DQ><InverseQ>akk7czcJJC2jbbWVPnxJM3/Z1DMF1LjuqidcboZsHQ440XZrGClDx0qrfD4ZGM52GWjxPpSabHk4jIKsP57yfA==</InverseQ><D>JoGyYA7C0IbTPRNNnJe4Kq70cUa29eTnpsC7J77e6n3a/Uss3mxhU91jE60CtTQoFbt6jkwvhwpWJLTpHyJ3w4snw5vu85+DTXlQ7Y3LJTG5PP+TW9t+rB4xnKgC6KG2Qhst5bCvRYNuWaMO44ZPSphgOt+ho8jI2VrasYbtY9E=</D></RSAKeyValue>");
			var privateKey = RSA.ToXmlString(true);
			var publicKey = RSA.ToXmlString(false);

			var js = "a74c06de7a7cdbd419533b2811b9cef2705fb39d956de0055d1c8c9fd528f1c3d1d3c21e005dfe2d50a52675e49a0acb0d09da83298f666fdf4c0693c081a5e59a91d71654438c7f084b2aff8ad79e9bc54f96c461325505bde3fb36b5fdc9b9bc0b44588156b67841f88f20cc6e6a4447919d8df5229d0d1f4fb2de806520e2";
			var jsbyte = new byte[js.Length / 2];
			for (var i = 0; i < jsbyte.Length; i += 1)
			{
				var n = js.Substring(i * 2, 2);
				jsbyte[i] = byte.Parse(n, System.Globalization.NumberStyles.HexNumber);
			}
			var contentBuffer2 = RSA.Decrypt(jsbyte, false);
			var r2 = Encoding.UTF8.GetString(contentBuffer2);


			var xmldoc = new System.Xml.XmlDocument();
			xmldoc.LoadXml(publicKey);
			var pubKey = xmldoc.SelectSingleNode("RSAKeyValue/Modulus").InnerText;
			var exponent = xmldoc.SelectSingleNode("RSAKeyValue/Exponent").InnerText;

			pubKey = Helper.BitString(Convert.FromBase64String(pubKey));
			exponent = Helper.BitString(Convert.FromBase64String(exponent));

			var content = "abc";
			var contentBuffer = Encoding.UTF8.GetBytes(content);

			contentBuffer = RSA.Encrypt(contentBuffer, false);

			var enc = Helper.BitString(contentBuffer);


			contentBuffer = RSA.Decrypt(contentBuffer, false);

			

			content = Encoding.UTF8.GetString(contentBuffer);

		}

	}
}
